package alarmdisplay;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.*;
import java.sql.*;
import java.io.*;
import java.awt.*;
import javax.swing.*;

public class Datenbank {

    public String slon_lat = null;
    private static SimpleDateFormat formater = new SimpleDateFormat();
    private int MAX_BUTTON = 9;
    private String strUsername = "";
    private String strPassword = "";
    private String strDriver = "com.mysql.jdbc.Driver"; //com.mysql.jdbc.Driver
    private String strURL = "";
    private Connection connection;

    public static void main(String[] args) {
        Debug.debug("DATENBANK", "DEBUG>> Datenbank V1.24 Start um " + formater.format(Calendar.getInstance().getTime()));
    }

    public Datenbank() {
        try {
            BufferedReader in = new BufferedReader(new FileReader("db.ini"));
            String str;
            while ((str = in.readLine()) != null) {
                if (str.startsWith("USER=")) {
                    strUsername = str.substring(5, str.length());
                    Debug.debug("DATENBANK", "DEBUG>> DB-User: " + strUsername);
                }
                if (str.startsWith("PASS=")) {
                    strPassword = str.substring(5, str.length());
                    Debug.debug("DATENBANK", "DEBUG>> DB-Pass: XXX ");
                }
                if (str.startsWith("URL =")) {
                    strURL = str.substring(5, str.length());
                    Debug.debug("DATENBANK", "DEBUG>> DB-URL: " + strURL);
                }
            }
            in.close();
        } catch (IOException err) {
            Debug.debug("DATENBANK", "ERROR>> IOException: " + err);
        }
    }

    /**
     * DB-Connection herstellen
     */
    public boolean dbOpen() {
        try {
            Class.forName(strDriver);
            connection = DriverManager.getConnection(strURL, strUsername, strPassword);
        } catch (Exception err) {
            Debug.debug("DATENBANK", "ERROR>> Error Connecting with User:" + strUsername + " and Password:" + strPassword + " --- " + err);
            return false;
        }
        return true;
    }

    /**
     * DB-Connection schliessen
     */
    public void dbClose() {
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception e) {
            }
        }
    }

    /**
     * Insert in Nachrichten RAW
     */
    public void dbInsertRaw(String str) {
        String strInsert = "insert into nachrichten_raw (text) values (?)";

        if (dbOpen() == false) {
            return;
        }
        try {
            Debug.debug("DATENBANK", "DEBUG>> [INSERT]: " + formater.format(Calendar.getInstance().getTime()) + " --- " + str);
            PreparedStatement ps = connection.prepareStatement(strInsert);
            ps.setString(1, str);
            ps.executeUpdate();
            ps.close();
        } catch (SQLException err) {
            Debug.debug("DATENBANK", "ERROR>> " + err.toString());
        }
        dbClose();
    }

    /**
     * Abfrage der Orte für Karte
     */
    public String dbQueryOrt(String str, String strOrt) {
        String sPk1 = "-1", sVPk1 = "";
        String s1 = "", s2 = "";

        if (dbOpen() == false) {
            return "";
        }
        Debug.debug("DATENBANK", "DEBUG2>> " + strOrt);
        try {
            Debug.debug("DATENBANK", "DEBUG2>> select pk, vpk from orte_neu where concat(plz,' ',ort) = '" + strOrt + "'" + " or ort = '" + strOrt + "'");
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select pk, vpk from orte_neu where concat(plz,' ',ort) = '" + strOrt + "'" + " or ort = '" + strOrt + "'");
            while (rs.next()) {
                sPk1 = rs.getString(1);
                sVPk1 = rs.getString(2);
                Debug.debug("DATENBANK", "DEBUG2>> " + sPk1 + " VPK: " + sVPk1);
            }
            rs.close();
            stmt.close();

//            slon_lat = "-";                             // Vorbelegen mit Dummy-Wert
            stmt = connection.createStatement();
            rs = stmt.executeQuery("select orte.plz, orte.stadt, lat_lon from orte_neu orte where orte.pk = " + sPk1 + "");
            while (rs.next()) {
                s1 = rs.getString(1);
                s2 = rs.getString(2);
                slon_lat = rs.getString(3);
            }
            Debug.debug("DATENBANK", "DEBUG2>> PLZ: " + s1 + " Stadt: " + s2 + " - " + slon_lat);
            rs.close();
            if (slon_lat.equals("")) {
//                slon_lat = "";
                if (!sVPk1.equals("0")) {
                    rs = stmt.executeQuery("select orte.plz, orte.stadt, lat_lon from orte_neu orte where orte.pk = " + sVPk1 + "");
                    while (rs.next()) {
                        s1 = rs.getString(1);
                        s2 = rs.getString(2);
                        slon_lat = rs.getString(3);
                    }
                    Debug.debug("DATENBANK", "DEBUG2>> PLZ2: " + s1 + " Stadt2: " + s2 + " - " + slon_lat);
                    rs.close();
                }
            }
            stmt.close();
        } catch (SQLException err) {
            Debug.debug("DATENBANK", "ERROR2>> " + err.toString());
        }
        dbClose();
        return s1 + " " + s2;
    }

    /**
     * Abfrage der Fahrzeuge
     */
    public String dbQueryFahrzeuge(String str, String strOrt, JButton jb[]) {
        int x = 0;
        String strLangEinsatz = "";
        String sPk1 = "", sVPk1 = "";

        if (dbOpen() == false) {
            return "";
        }
        try {
            Debug.debug("DATENBANK", "DEBUG>> " + strOrt);
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select pk, vpk from orte_neu where concat(plz,' ',ort) = '" + strOrt + "'"
                    + "or ort = '" + strOrt + "'");	//BAB_WL oder 21534 BAD BEvens
            while (rs.next()) {
                sPk1 = rs.getString(1);
                sVPk1 = rs.getString(2);
                Debug.debug("DATENBANK", "DEBUG>> OrtPK (sPK: " + sPk1 + " Virtueller Ort (VPK): " + sVPk1);
            }
            rs.close();
            stmt.close();

            if (!sVPk1.equals("0")) {
                sPk1 = sVPk1;
            }

            stmt = connection.createStatement();
            rs = stmt.executeQuery("select fahrzeuge.fzg, stichworte.bezeichnung, orte.ort from stichworte, fahrzeuge, aao_neu aao, orte_neu orte where fahrzeuge.pk = aao.f_pk and stichworte.pk = aao.s_pk "
                    + "and orte.pk = aao.o_pk and orte.pk = '" + sPk1 + "'"
                    + "and stichworte.stichwort in (select IF(stichworte.verknuepfung is null, stichworte.stichwort, stichworte.verknuepfung) "
                    + "from stichworte where stichworte.stichwort = '" + str + "') group by 1 order by aao.folge asc");
            while (rs.next()) {
                String s1 = rs.getString(1);
                strLangEinsatz = rs.getString(2);
                jb[x].setFont(new Font("Arial", Font.BOLD, 70));
                jb[x].setBackground(Color.RED);
                jb[x++].setText(s1);
                Debug.debug("DATENBANK", "DEBUG>> " + s1 + " Lang: " + strLangEinsatz);
            }
            rs.close();
            
            //Stichwort überschreiben, da sonst die Verknüpfung angezeigt werden könnte
            rs = stmt.executeQuery("select fahrzeuge.fzg, stichworte.bezeichnung, orte.ort from stichworte, fahrzeuge, aao_neu aao, orte_neu orte "
                                 + "where fahrzeuge.pk = aao.f_pk and stichworte.pk = aao.s_pk "
                                 + "and orte.pk = aao.o_pk and orte.pk = '" + sPk1 + "' and stichworte.stichwort = '" + str + "'");
            while (rs.next()) {
                strLangEinsatz = rs.getString(2);
                Debug.debug("DATENBANK", "DEBUG>> Lang: " + strLangEinsatz);
            }
            rs.close();
            stmt.close();
            
            
            for (; x < MAX_BUTTON; x++) {
                jb[x].setText("");
                jb[x].setBackground(Color.LIGHT_GRAY);
            }

            if (strLangEinsatz.equals("")) {
                Debug.debug("DATENBANK", "DEBUG >> Kombi AAO/Fzg nicht gefunden!" + str);
                stmt = connection.createStatement();
                rs = stmt.executeQuery("select bezeichnung from stichworte where stichwort = '" + str + "'");
                while (rs.next()) {
                    strLangEinsatz = rs.getString(1);
                }
                rs.close();
                stmt.close();
                jb[0].setFont(new Font("Arial", Font.BOLD, 70));
                jb[0].setBackground(Color.RED);
                jb[0].setText("ACHTUNG");
                jb[1].setFont(new Font("Arial", Font.BOLD, 70));
                jb[1].setBackground(Color.RED);
                jb[1].setText("---");
                jb[2].setFont(new Font("Arial", Font.BOLD, 70));
                jb[2].setBackground(Color.RED);
                jb[2].setText("Fahrzeuge");
                jb[3].setFont(new Font("Arial", Font.BOLD, 70));
                jb[3].setBackground(Color.RED);
                jb[3].setText("nach");
                jb[4].setFont(new Font("Arial", Font.BOLD, 70));
                jb[4].setBackground(Color.RED);
                jb[4].setText("Weisung!");
            }
        } catch (SQLException err) {
            Debug.debug("DATENBANK", "ERROR>> " + err.toString());
        }
        dbClose();
        return strLangEinsatz;
    }

    /**
     * Abfrage der RIC
     */
    public boolean dbCheckRIC(String str) {
        String s1 = "dada";
        if (dbOpen() == false) {
            return false;
        }
        try {
            Debug.debug("DATENBANK", "DEBUG>> RIC start");
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select value from config where name = 'comlistener_ric' and value = '" + str + "'");
            while (rs.next()) {
                s1 = rs.getString(1);
                Debug.debug("DATENBANK", "DEBUG>> RIC:" + s1);
            }
            rs.close();
            stmt.close();
            dbClose();
            if (s1.equals("dada")) {
                Debug.debug("DATENBANK", "DEBUG>> RIC return false");
                return false;
            }
        } catch (SQLException err) {
            Debug.debug("DATENBANK", "ERROR>> " + err.toString());
        }
        Debug.debug("DATENBANK", "DEBUG>> RIC return true");
        return true;
    }

    /**
     * Abfrage von Konfigdaten
     */
    public String dbQueryConfig(String str) {
        String s1 = null;

        if (dbOpen() == false) {
            return "";
        }
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select value from config where name = '" + str + "'");
            while (rs.next()) {
                s1 = rs.getString(1);
                if (str.equals("mail_server_pw")) {
                    Debug.debug("DATENBANK", "DEBUG>> Config: " + str + " --> XXX");
                } else {
                    Debug.debug("DATENBANK", "DEBUG>> Config: " + str + " --> " + s1);
                }
            }
            rs.close();
            stmt.close();
        } catch (SQLException err) {
            Debug.debug("DATENBANK", "ERROR>> " + err.toString());
            return "";
        }
        dbClose();
        return s1;
    }

    /**
     * Abfrage von EMaildaten
     */
    public Vector<String> dbQueryEMail(String sConfig) {
        Vector<String> v = new Vector<String>();
        String s1 = "";

        if (dbOpen() == false) {
            return v;
        }
        try {
            Statement stmt = connection.createStatement();
//            ResultSet rs = stmt.executeQuery("select value from config where name = 'mail_recipient'");
//            ResultSet rs = stmt.executeQuery("select value from config where name = 'mail_recipient_2'");
            ResultSet rs = stmt.executeQuery("select value from config where name = '" + sConfig + "'");
            while (rs.next()) {
                s1 = rs.getString(1);
                v.addElement(s1);
                Debug.debug("DATENBANK", "DEBUG>> EMail: " + s1);
            }
            rs.close();
            stmt.close();
        } catch (SQLException err) {
            Debug.debug("DATENBANK", "ERROR>> " + err.toString());
            return v;
        }
        dbClose();
        return v;
    }

    /**
     * Abfrage von SMS-Adressen
     */
    public Vector<String> dbQuerySMS(String sConfig) {
        Vector<String> v = new Vector<String>();
        String s1 = "";

        if (dbOpen() == false) {
            return v;
        }
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select value from config where name = '" + sConfig + "'");
            while (rs.next()) {
                s1 = rs.getString(1);
                v.addElement(s1);
                Debug.debug("DATENBANK", "DEBUG>> SMS: " + s1);
            }
            rs.close();
            stmt.close();
        } catch (SQLException err) {
            Debug.debug("DATENBANK", "ERROR>> " + err.toString());
            return v;
        }
        dbClose();
        return v;
    }


    /**
     * Abfrage von TCPEmpfaengern
     */
    public Vector<ComIPPort> dbQueryCom() {
        Vector<ComIPPort> v = new Vector<ComIPPort>();
        String s1 = "";
        int i1 = -1, i2 = 0;

        if (dbOpen() == false) {
            return v;
        }
        try {
            // First run for IP's
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select value from config where name like 'comlistener_sender_ip_%' order by name");
            while (rs.next()) {
                s1 = rs.getString(1);
                v.addElement(new ComIPPort(s1, 0));
                Debug.debug("DATENBANK", "DEBUG>> IP: " + s1);
            }
            rs.close();
            stmt.close();

            // Second run for Ports
            stmt = connection.createStatement();
            rs = stmt.executeQuery("select value from config where name like 'comlistener_sender_port_%' order by name");
            while (rs.next()) {
                i1 = Integer.parseInt(rs.getString(1));
                ComIPPort cip = v.elementAt(i2);
                cip.iPort = i1;
                v.setElementAt(cip, i2);
                i2++;
                Debug.debug("DATENBANK", "DEBUG>> Port: " + i1 + ", IP: " + cip.sIP);
            }
            rs.close();
            stmt.close();

        } catch (SQLException err) {
            Debug.debug("DATENBANK", "ERROR>> " + err.toString());
            return v;
        }
        dbClose();
        return v;
    }

}

class ComIPPort {

    public String sIP;
    public int iPort;

    public ComIPPort(String sIP, int iPort) {
        this.sIP = sIP;
        this.iPort = iPort;
    }
}
